package com.second.ssyt_test.assist.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;








import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;

import org.apache.commons.lang3.CharUtils;
import org.apache.commons.lang3.Validate;
import org.apache.log4j.Logger;
import org.apache.poi.xwpf.usermodel.TOC;

import com.second.ssyt_test.assist.entity.AssistEntity;
import com.second.ssyt_test.common.Constant;
import com.second.ssyt_test.common.exception.CommonRuntimeException;
import com.second.ssyt_test.common.util.DBUtil;
import com.second.ssyt_test.common.util.LogUtil;
import com.second.ssyt_test.sys.entity.SysEntity;
import com.sun.org.apache.regexp.internal.recompile;

public class AssistDao {
	private static final Logger LOG = Logger.getLogger(DBUtil.class);
    /**
     * 查询备份数据
     * @return
     */
	public static List<AssistEntity> list() {
		//SQL语句
		String sql = "SELECT * FROM assit_db_data";
		//对应实体结构
		Class<AssistEntity> clazz = AssistEntity.class;
		List<AssistEntity> AssistEntityList =  new ArrayList<AssistEntity>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			// 读取取数据库结构
			ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
			// 得到有多少列
			int columns = resultSetMetaData.getColumnCount();
			// 循环每条结果，并把结果封装成具体的实体对象，最后放到返回集合中
			while (resultSet.next()) {
				AssistEntity obj = clazz.newInstance();
				// 对每列循环，把对应的值赋对obj的相应属性
				for (int i = 1; i <= columns; i++) {
					String columnLabel = resultSetMetaData.getColumnLabel(i);
					// 循环所有的属性
					Field[] fields = clazz.getDeclaredFields();
					for (Field field : fields) {
						field.setAccessible(true);
						Class<?> fieldClass = field.getType();
						String fieldName = field.getName();
						// 某个属性有大写，表示需要属性名与数据库列名对应
						if (isContainsAlphaUpper(fieldName)) {
							String columnName = toColumnName(fieldName);
							if (columnName.equals(columnLabel)) {
								setFieldValue(resultSet, obj, columnLabel, field, fieldClass);
								break;
							}
						} else {
							// 表示属性名与数据库列名相同
							if (fieldName.equals(columnLabel)) {
								setFieldValue(resultSet, obj, columnLabel, field, fieldClass);
								break;
							}
						}
					}
				}
				AssistEntityList.add(obj);
			}
			return AssistEntityList;
		} catch (SQLException | InstantiationException | IllegalAccessException | IllegalArgumentException
				| SecurityException e) {
			//LogUtil.e(LOG, "数据查询失败", e);
			throw new CommonRuntimeException();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 *验证
	 * @param resultSet
	 * @param obj
	 * @param columnLabel
	 * @param field
	 * @param fieldClass
	 */
	private static <T> void setFieldValue(ResultSet resultSet, T obj, String columnName, Field field,
			Class<?> fieldClass) throws IllegalAccessException, SQLException {
		Validate.notNull(resultSet, "resultSet" + Constant.VALIDATE_NOT_NULL);
		Validate.notNull(obj, "obj" + Constant.VALIDATE_NOT_NULL);
		Validate.notBlank(columnName, "columnName" + Constant.VALIDATE_NOT_BLANK);
		Validate.notNull(field, "field" + Constant.VALIDATE_NOT_NULL);
		Validate.notNull(fieldClass, "fieldClass" + Constant.VALIDATE_NOT_NULL);

		// FIXME 根据业务扩展
		if (fieldClass.equals(int.class) || fieldClass.equals(Integer.class)) {
			field.set(obj, resultSet.getInt(columnName));
		} else if (fieldClass.equals(String.class)) {
			field.set(obj, resultSet.getString(columnName));
		} else if (fieldClass.equals(BigDecimal.class)) {
			field.set(obj, resultSet.getBigDecimal(columnName));
		} else if (fieldClass.equals(Date.class)) {
			field.set(obj, resultSet.getTimestamp(columnName));
		}
	}
	/**
	 * Java属性 -> 数据库字段名：helloWorld -> hello_world
	 * 
	 * @param fieldName
	 * @return
	 */
	private static String toColumnName(String fieldName) {
		if (fieldName == null) {
			return "";
		}

		char[] chars = fieldName.toCharArray();
		StringBuilder sb = new StringBuilder();
		for (char c : chars) {
			if (CharUtils.isAsciiAlphaUpper(c)) {
				sb.append("_" + (char) (c + 32));
			} else {
				sb.append(c);
			}
		}
		return sb.toString();
	}
	/**
	 * 判断字符串里是否有大写字母
	 * 
	 * @param fieldName
	 * @return
	 */
	private static boolean isContainsAlphaUpper(String fieldName) {
		Validate.notNull(fieldName);

		for (int i = 0; i < fieldName.length(); i++) {
			if (CharUtils.isAsciiAlphaUpper(fieldName.charAt(i))) {
				return true;
			}
		}
		return false;
	}
    /**
     * 添加数据备份
     * @return
     */
	public static int addbackups(String backupsmemeo) {
		String user = "root"; // 数据库帐号
    	String password = "root"; // 登陆密码
    	String database = "yec"; // 需要备份的数据库名
    	String filepath = "e:\\mysqlbackup\\yec.sql"; // 备份的路径地址

    	String stmt1 = "mysqldump " + database + " -u " + user + " -p"
    	+ password + " --result-file=" + filepath;
    	try {
    	Runtime.getRuntime().exec(stmt1);
    	}
    	catch (IOException e) {
    	e.printStackTrace();
    	}
		String sql= "INSERT assit_db_data VALUES(NULL,?,NOW(),?,NOW(),?,?,?)";
		List<Object> paramList = new ArrayList<>();
		paramList.add(filepath);
		paramList.add(backupsmemeo);
		paramList.add(backupsmemeo);
		paramList.add(1);
		paramList.add(1);
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			setParameter(paramList, preparedStatement);
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			LogUtil.e(LOG, "数据更新失败", e);
			throw new CommonRuntimeException();
		} finally {
			DBUtil.close(connection, preparedStatement);
		}
	}
	/**
	 * 参数设置
	 * 
	 * @param paramList
	 * @param preparedStatement
	 * @throws SQLException
	 */
	public static void setParameter(List<Object> paramList, PreparedStatement preparedStatement) throws SQLException {
		if (paramList != null) {
			for (int i = 0; i < paramList.size(); i++) {
				preparedStatement.setObject(i + 1, paramList.get(i));
			}
		}
	}
    
}//servlet
